<?php
class Sanpham_model extends CI_Model {

  	protected $_gallery_path = "";
    protected $_gallery_url = "";
    public function __construct(){
        parent::__construct();
        //Lấy đường dẫn url của thư mục chứa hình ảnh được upload
        $this->_gallery_url = base_url()."images/product/";
        //Lấy đường dẫn vật lý của thư mục chứa hình ảnh đươc upload
        $this->_gallery_path = realpath(APPPATH. "../images/product");
    }
	
	function getSanPham($mataikhoan) {
    	//$this->load->database();
    	$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, s.GiaBan, l.TenLoaiSP, s.TrangThai from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = ?";
    	return $this->db->query($sql, array($mataikhoan))->result_array();
    }
	
	function getLoaiSPCha(){
		$sql = "select * from loaisp where maloaicha = 0";
		return $this->db->query($sql)->result_array();
	}
	
	function getLoaiSPCon($maloaicha){
		$sql = "select * from loaisp where maloaicha = ?";
		return $this->db->query($sql, array($maloaicha))->result_array();
	}
	
	//function getCha($maloai){
		//$sql = "SELECT maloaisp FROM loaisp WHERE maloaicha = ?";
		//return $this->db->query($sql, array($maloai))->result_array();
	//}
	
	function getSanPham_ajax($mataikhoan, $maloai){
		if($maloai != 1){
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, s.GiaBan, l.TenLoaiSP, s.TrangThai from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = $mataikhoan and s.maloaisp in(SELECT maloaisp FROM loaisp WHERE maloaicha = $maloai union select maloaicha from loaisp where maloaicha = $maloai)";
		}else{
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, s.GiaBan, l.TenLoaiSP, s.TrangThai from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = $mataikhoan";
		}
    	return $this->db->query($sql)->result_array();
	}
	
	function getSanPham_ajax_con($mataikhoan, $maloaicon, $maloaicha){
		if($maloaicon != 1){
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d') as NgayDang, s.GiaBan, l.TenLoaiSP, s.TrangThai from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang  = ? and s.maloaisp = ?";	
			return $this->db->query($sql, array($mataikhoan, $maloaicon))->result_array();
		}
		else{
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, s.GiaBan, l.TenLoaiSP, s.TrangThai from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = ? and s.maloaisp in(SELECT maloaisp FROM loaisp WHERE maloaicha = ? union select maloaicha from loaisp where maloaicha = ?)";
			return $this->db->query($sql, array($mataikhoan, $maloaicha, $maloaicha))->result_array();
		}
	}
	
	function dangsanpham($mataikhoan,$ten,$noidung,$loaicha,$loaicon,$gia,$hinhanh){
		//Neu chon loai con thi them vao loai con, khong chon thi them vao loai cha
		if($loaicon != "1"){
			$sql = "insert into sanpham(TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,NOW(),?,0,?,1)"	;
			$this->db->query($sql, array($ten,$hinhanh,$noidung,$loaicon,$gia));
		}else{
			$sql = "insert into sanpham(TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,NOW(),?,0,?,1)"	;
			$this->db->query($sql, array($ten,$hinhanh,$noidung,$loaicha,$gia));	
		}
		
		//Lay san pham vua dang
		$sql = "select max(masanpham) as masp from sanpham";
		$masp_moi = $this->db->query($sql)->row(0)->masp;
		
		//Kiem tra nguoi dang da ton tai hay chua?
		$sql = "select * from nguoidang where mataikhoan = ?";
		if($this->db->query($sql, array($mataikhoan))->num_rows() == 0){
			$sql = "insert into nguoidang(mataikhoan, diemnguoidang) values(?, 0)";
			$this->db->query($sql, array($mataikhoan));
		}
		
		//Them vao bang dang san pham
		$sql = "insert into dangsanpham(MaSanPham, MaNguoiDang) values(?,?)";
		$this->db->query($sql, array($masp_moi,$mataikhoan));
		
		//Xoa du lieu bang tam
		$sql = "update bangtam set tensanpham = '', mota = '' where mataikhoan = $mataikhoan";
		$this->db->query($sql);
		
	}
	
	function do_upload(){
		$config = array('upload_path'   => $this->_gallery_path,
                        'allowed_types' => 'gif|jpg|png|jpeg',
                        'max_size'      => '2000');
        $this->load->library("upload",$config);
        if(!$this->upload->do_upload("hinh_anh")){
            $error = array($this->upload->display_errors());
        }else{
            $image_data = $this->upload->data();    
        }
        //kết thúc công đoạn upload hình ảnh
        
        $config = array("source_image" => $image_data['full_path'],
                        "new_image" => $this->_gallery_path . "/thumbs",
                        "maintain_ration" => true,
                        "width" => '150',
                        "height" => "100");
        $this->load->library("image_lib",$config);
        $this->image_lib->resize();
        //kết thúc công đoạn resize lại hình ảnh
	}
	
	function getSPTam($mataikhoan){
		$sql = "select tensanpham, mota from bangtam where mataikhoan = $mataikhoan"	;
		return $this->db->query($sql)->result_array();
	}
	
	function autoSave($tensp,$mota,$mataikhoan){
		
		$sql = "update bangtam set tensanpham = ?, mota = ? where  mataikhoan = ?";
		$this->db->query($sql, array($tensp, $mota, $mataikhoan));
		
	}
	
	function getChuyenMucChaSP($masanpham){
		$sql = "select maloaisp from sanpham where masanpham = ?";
		$loaicon = $this->db->query($sql, array($masanpham))->row(0)->maloaisp;
		
		$sql = "select maloaicha from loaisp where maloaisp = ?";
		if($this->db->query($sql, array($loaicon))->row(0)->maloaicha == 0){
			return $loaicon;	
		}else{
			return $this->db->query($sql, array($loaicon))->row(0)->maloaicha;
		}
	}
	
	function getChuyenMucConSP($masanpham){
		$sql = "select maloaisp from sanpham where masanpham = ?";
		$loaicon = $this->db->query($sql, array($masanpham))->row(0)->maloaisp;
		
		$sql = "select maloaicha from loaisp where maloaisp = ?";
		if($this->db->query($sql, array($loaicon))->row(0)->maloaicha == 0){
			return 1;	
		}else{
			return $loaicon;
		}
	}
	
	function getSanPhamSua($mataikhoan,$masanpham){
		$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, s.GiaBan from sanpham s, dangsanpham d, loaisp l where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = ? and s.masanpham = ?";
    	return $this->db->query($sql, array($mataikhoan, $masanpham))->result_array();
	}
	
	function getDSSPCha(){
		$sql = "select * from loaisp where maloaicha = 0 and maloaisp <> 1";
		return $this->db->query($sql)->result_array();
	}
	
	function getDSSPCon($cha){
		$sql = "select * from loaisp where maloaicha <> 0 and maloaicha = ?";
		return $this->db->query($sql, $cha)->result_array();
	}
	
	function suasanpham($masanpham,$mataikhoan,$ten,$noidung,$loaicha,$loaicon,$gia,$hinhanh,$anh_cu){
		$this->load->database();
		
		$hinhanh = str_replace(" ", "_", $hinhanh);
		//Xoa san pham da dang
		$sql = "delete from sanpham where MaSanPham = ?";
		$this->db->query($sql,array($masanpham));
		
		//Xoa anh san pham cu
		unlink("./images/product/".$anh_cu);
		
		//Xoa dang san pham
		$sql = "delete from dangsanpham where MaSanPham = ?";
		$this->db->query($sql,array($masanpham));
		
		if($hinhanh != ""){//Neu thay hinh anh moi
			//Neu chon loai con thi them vao loai con, khong chon thi them vao loai cha
			if($loaicon != "1"){
				$sql = "insert into sanpham(MaSanPham, TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,?,NOW(),?,0,?,1)"	;
				$this->db->query($sql, array($masanpham,$ten,$hinhanh,$noidung,$loaicon,$gia));
			}else{
				$sql = "insert into sanpham(MaSanPham, TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,?,NOW(),?,0,?,1)"	;
				$this->db->query($sql, array($masanpham,$ten,$hinhanh,$noidung,$loaicha,$gia));	
			}

		}else{//Neu khong thay hinh anh moi thi giu lai hinh anh cu
			if($loaicon != "1"){
				$sql = "insert into sanpham(MaSanPham, TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,?,NOW(),?,0,?,1)"	;
				$this->db->query($sql, array($masanpham,$ten,$anh_cu,$noidung,$loaicon,$gia));
			}else{
				$sql = "insert into sanpham(MaSanPham, TenSanPham, AnhSanPham, MoTa, NgayDang, MaLoaiSP, DiemDanhGia, GiaBan, TrangThai) values(?,?,?,?,NOW(),?,0,?,1)"	;
				$this->db->query($sql, array($masanpham,$ten,$anh_cu,$noidung,$loaicha,$gia));	
			}
		}
		
		//Kiem tra nguoi dang da ton tai hay chua?
		$sql = "select * from nguoidang where mataikhoan = ?";
		if($this->db->query($sql, array($mataikhoan))->num_rows() == 0){
			$sql = "insert into nguoidang(mataikhoan, diemnguoidang) values(?, 0)";
			$this->db->query($sql, array($mataikhoan));
		}
		
		//Them vao bang dang san pham
		$sql = "insert into dangsanpham(MaSanPham, MaNguoiDang) values(?,?)";
		$this->db->query($sql, array($masanpham,$mataikhoan));
		
		//Xoa du lieu bang tam
		$sql = "update bangtam set tensanpham = '', mota = '' where mataikhoan = ?";
		$this->db->query($sql, array($mataikhoan));

	}
		
	function xoasanpham($masanpham){
		$this->load->database();
		
		//Xoa anh san pham
		$sql = "select AnhSanPham from sanpham where MaSanPham = ?";
		$anhsp = $this->db->query($sql,array($masanpham))->row(0)->AnhSanPham;
		unlink("./images/product/".$anhsp);
		
		//Xoa san pham da dang
		$sql = "delete from sanpham where MaSanPham = ?";
		
		//Xoa binh luan lien quan den san pham
		$sql1 = "delete from binhluan where MaSanPham = ?";

		//Xoa dang san pham
		$sql2 = "delete from dangsanpham where MaSanPham = ?";
		if ($this->db->query($sql,array($masanpham))==false || $this->db->query($sql1,array($masanpham))==false || $this->db->query($sql2,array($masanpham))==false)
    	{
    		return false;
    	}
    	return true;	
	}
	
	function getTatCaSP() {
    	//$this->load->database();
    	$sql = "SELECT s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT( s.NgayDang,  '%Y-%m-%d' ) AS NgayDang, t.TenDangNhap, s.GiaBan, l.TenLoaiSP
				FROM sanpham s, dangsanpham d, loaisp l, taikhoan t, nguoidang n
				WHERE s.masanpham = d.masanpham
				AND s.maloaisp = l.maloaisp
				AND d.manguoidang = n.manguoidang
				AND n.mataikhoan = t.mataikhoan";
    	return $this->db->query($sql, array($mataikhoan))->result_array();
    }
	
	function getSanPham_admin_ajax($maloai){
		if($maloai != 1){
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, t.TenDangNhap, s.GiaBan, l.TenLoaiSP from sanpham s, dangsanpham d, loaisp l, taikhoan t, nguoidang n where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = n.manguoidang and n.mataikhoan = t.mataikhoan and s.maloaisp in(SELECT maloaisp FROM loaisp WHERE maloaicha = $maloai union select maloaicha from loaisp where maloaicha = $maloai)";
		}else{
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, t.TenDangNhap, s.GiaBan, l.TenLoaiSP from sanpham s, dangsanpham d, loaisp l, taikhoan t, nguoidang n where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = n.manguoidang and n.mataikhoan = t.mataikhoan";
		}
    	return $this->db->query($sql)->result_array();
	}
	
	function getSanPham_ajax_con_admin($maloaicon, $maloaicha){
		if($maloaicon != 1){
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, t.TenDangNhap, s.GiaBan, l.TenLoaiSP from sanpham s, dangsanpham d, loaisp l, taikhoan t, nguoidang n where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = n.manguoidang and n.mataikhoan = t.mataikhoan and s.maloaisp = ?";	
			return $this->db->query($sql, array($maloaicon))->result_array();
		}
		else{
			$sql = "select s.MaSanPham, s.TenSanPham, s.AnhSanPham, s.MoTa, DATE_FORMAT(s.NgayDang, '%Y-%m-%d' ) AS NgayDang, t.TenDangNhap, s.GiaBan, l.TenLoaiSP from sanpham s, dangsanpham d, loaisp l, taikhoan t, nguoidang n where s.masanpham = d.masanpham and s.maloaisp = l.maloaisp and d.manguoidang = n.manguoidang and n.mataikhoan = t.mataikhoan and s.maloaisp in(SELECT maloaisp FROM loaisp WHERE maloaicha = ? union select maloaicha from loaisp where maloaicha = ?)";
			return $this->db->query($sql, array($maloaicha, $maloaicha))->result_array();
		}
	}
	
	function hethang($masanpham){
		$sql = "update sanpham set trangthai = 0 where masanpham = ?";
		$this->db->query($sql, array($masanpham));
	}
	
	function getSoSP($tk){
		for($i=0; $i<count($tk); $i++){
			$sql = "SELECT count(MaSanPham) as SoSP FROM dangsanpham dsp, nguoidang nd where nd.manguoidang = dsp.manguoidang and nd.mataikhoan = ?";
			$res = $this->db->query($sql, array($tk[$i]['MaTaiKhoan']))->result_array();
			$sosanpham[$i] = $res[0]['SoSP'];
		}
		
		return $sosanpham;
	}
	
	//Loc: Lay san pham khi click vao danh muc menu
	function laysanphamtheomasp($masp)
    {
        $query = 'SELECT sp.MaSanPham, sp.TenSanPham, sp.AnhSanPham, DATE_FORMAT(sp.NgayDang, \'%d-%m-%Y\') as NgayDang, sp.DiemDanhGia, sp.GiaBan, sp.TrangThai
        FROM sanpham sp, loaisp l
        WHERE (
            l.MaloaiSP =?
        OR l.MaloaiCha =?
        )
        AND l.MaLoaiSP = sp.MaLoaiSP
        ORDER BY DiemDanhGia DESC';
        return $this->db->query($query, array($masp, $masp))->result_array();
    }
	
	//Loc: Lay loai san pham de lam menu
	function layloaisanpham()
	{
		$query = "select * from loaisp";
		return $this->db->query($query)->result_array();
	}
    
    //Loc: Lay san pham theo masp
    function laythongtinsanphamtheomasp($masp)
    {
        $query = "select sanpham.MaSanPham, TenSanPham, AnhSanPham, MoTa, DATE_FORMAT(sanpham.NgayDang, '%d-%m-%Y') as NgayDang, 
                    loaisp.TenLoaiSP, DiemDanhGia, TrangThai, GiaBan
                    from sanpham, loaisp
                    where sanpham.MaLoaiSP= loaisp.MaLoaiSP and sanpham.MaSanPham = ?";
        return $this->db->query($query, array($masp))->result_array();
    }
    function kiemtradanhgia($mataikhoan, $masp)
    {
        $query = "select * from danhgia, nguoidanhgia, taikhoan
                    where danhgia.MaNguoiDanhGia = nguoidanhgia.MaNguoiDanhGia
                    and nguoidanhgia.MaTaiKhoan = taikhoan.MaTaiKhoan
                    and taikhoan.MaTaiKhoan = ?
                    and danhgia.MaSanPham= ?";
        $rows = $this->db->query($query, array($mataikhoan, $masp))->num_rows();
        $query1 = "select * from nguoidanhgia
                    where MaTaiKhoan = ?";
        $row_quyen = $this->db->query($query1, array($mataikhoan))->num_rows();
        if($mataikhoan = null || $mataikhoan == '')
            $ketqua = -1;
        else
        {
            if($row_quyen == 0)
                $ketqua = 2;
            else
            {
                if( $rows > 0)
                    $ketqua = 0;
                else
                    $ketqua = 1;
            }
        }
        return $ketqua;
    }
    function danhgiasanpham($masp, $mataikhoan, $uytin, $chatluong, $gia)
    {
        $query = "select * from nguoidanhgia where MaTaiKhoan = ?";
        $query1 = "insert into danhgia values(?,?,?,?,?)";
        $query2 = "select (4*SUM(DiemUyTin) + 3*SUM(DiemChatLuong) + 3*SUM(DiemGiaCa))/(10*COUNT(MaSanPham)) as ketqua 
                    from danhgia where MaSanPham = ?";
        $query3 = "update sanpham set DiemDanhGia = ? where MaSanPham = ?";
        $manguoidang = $this->db->query($query, array($mataikhoan))->row('MaNguoiDanhGia');
        $this->db->query($query1, array($masp, $manguoidang, $uytin, $chatluong, $gia));
        $diemsp = $this->db->query($query2, array($masp))->row('ketqua');
        $this->db->query($query3, array($diemsp, $masp));
        echo $diemsp;
    }
    function sanphammoi()
    {
        $query = "select MaSanPham, TenSanPham, AnhSanPham, GiaBan, NgayDang
                    from sanpham
                    order by NgayDang DESC
                    limit 0, 10";
        return $this->db->query($query)->result_array();
    }
    function timsanpham($timkiem)
    {
        $query ="select sanpham.MaSanPham, TenSanPham, AnhSanPham, MoTa, DATE_FORMAT(sanpham.NgayDang, '%d-%m-%Y') as NgayDang, 
                    loaisp.TenLoaiSP, DiemDanhGia, TrangThai, GiaBan
                    from sanpham, loaisp
                    where sanpham.MaLoaiSP= loaisp.MaLoaiSP and (TenSanPham like('%".$timkiem."%') or MoTa like('%".$timkiem."%'))
                    ORDER BY DiemDanhGia DESC";
        return $this->db->query($query)->result_array();
    }
}